package com.gmail.drheiniger.backend;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JTable;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

///the preparestatement getters and setters don't seem to be working

public class Database {
	// constants
	private static final String GET_MAX_BOOKID_STATEMENT = "Select MAX(BOOK_ID) FROM BOOK";
	private static final String INSERT_BOOK_STMT = "INSERT INTO BOOK (BOOK_ID, TITLE, AUTHOR, YEAR_READ, RATING, CONTENT) VALUES(?, ?, ?, ?, ?, ?)";
	private static final String SEARCH_STMT = "SELECT * FROM BOOK WHERE Title LIKE ? AND Author LIKE ? AND Year_Read LIKE ? AND Rating LIKE ? AND Content LIKE ?";
	private static final String GET_BOOK_COUNT_STMT = "SELECT COUNT(Book_ID) FROM Book";
	private static final String GET_ALL_BOOKS_STMT = "SELECT * FROM BOOK";

	// by changing 'query' to static, i keep the data that is passed to it from
	// searchBooksGUI. i think without static, it is erased in BrowseGUI,
	// because BrowseGUI creates a new database object.
	private int columnCount;
	private PreparedStatement pst;
	private ResultSetMetaData md;;
	// private ResultSet rs = null;
	// used for data from database
	private Vector<String> data;
	private Vector<String> row;	
	private JTable table;

	// private String query;

	public Database() {
		columnCount = 0;
		pst = null;
		md = null;
		data = new Vector<String>();
		row = new Vector<String>();
		// query = null;
	}

	public Connection getConnection() {
		String url = "jdbc:mysql://127.0.0.1:3306/book_db";
//		String url = "jdbc:mysql://96.126.114.16:3306/book_db";
		String user = "root";
//		String user = "david";
		String password = "5ar9dr12";
//		String password = "admin";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, user, password);

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(Database.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
		}
		return con;
	}

	public void createTable() {
		// System.out.println("create table " + pst.toString());
		// System.out.println("Made it to createTable().  query = " + query);
		int rowCount = 0;
		// PreparedStatement pst = null;
		ResultSet rs = null;

//		DefaultTableModel tableModel = new DefaultTableModel(){
//			@Override
//			public boolean isCellEditable(int row, intColumn){
//				return false;
//			}
//			table.setModel(tableModel);
//		};
		DefaultTableModel tableModel = new DefaultTableModel(getBookCount(), 6) {
			
			/**
			 * 
			 */
			private static final long serialVersionUID = 1L;

			//this method is overidden so that it isn't possible to select/edit the rows.
			@Override
			public boolean isCellEditable(int row, int column){
				return false;
			}
		};
		
//		table.setModel(tableModel);
		// create headers. There is probably a better way
		/*
		 * Vector<String> header = new Vector<String>(); header.addElement(new
		 * String("Book_ID")); header.addElement(new String("Title"));
		 * header.addElement(new String("Author")); header.addElement(new
		 * String("Year")); header.addElement(new String("Rating"));
		 * header.addElement(new String("Content"));
		 */
		try {

//			System.out.println("right before executequery ");
			if (pst == null) {
				pst = getConnection().prepareStatement(GET_ALL_BOOKS_STMT);
			}
			// System.out.println("pst = " + pst);
			PreparedStatement test = pst;
//			System.out.println(test + " right before execution.");
			rs = pst.executeQuery();
			pst = null;
			md = rs.getMetaData();
			columnCount = md.getColumnCount();

			while (rs.next()) {
				// create a new vector for each row. this will hopefully solve
				// the 1 row problem.
				row = new Vector<String>();
				// row.addAll(header);
				for (int i = 1; i <= columnCount; i++) {
					row.add(rs.getString(i));
				}
				// the first parameter is the index, the second is the data
				// stored in the row
				tableModel.insertRow(rowCount, row);
				rowCount++;
				data.add(row.toString());
				// Debugging
				// System.out.println("Title = " + rs.getString("Title"));
				// System.out.println("Author = " + rs.getString("Author"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		// create a JTable to store the data in. I don't have to set dimensions
		// because the tableModel will already have them.

		// add the tablemodel to the table
	
		table = new JTable(tableModel);
		table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		table.getTableHeader().setReorderingAllowed(false);

//		table.getTableHeader().setResizingAllowed(false);
//		table.setSelectionModel(listSelectionModel);
//		listSelectionModel = table.getSelectionModel();
		// table.add(new ListSelectionEvent(arg0, arg1, arg2, arg3))
		setTable(table);
//		return table;
	}

	public void setTable(JTable table){
		this.table = table;
	}

	public JTable getTable(){
		return table;
	}
	// I think this is my first ever 'getter' method;
	public int getColumnCount() {
		return columnCount;
	}

//	public ListSelectionModel getListSelection() {
//		return listSelectionModel;
//	}

	// public String getQuery() {
	// System.out.println("made it to getQuery.  Query = " + query);
	// return query;
	// }

	public int getMaxBookID() {
		int maxID = 0;
		PreparedStatement pst = null;
		try {
			pst = getConnection().prepareStatement(GET_MAX_BOOKID_STATEMENT);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				maxID = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return maxID;
	}

	public int getBookCount() {
		int bookCount = 0;
		PreparedStatement pst = null;
		try {
			pst = getConnection().prepareStatement(GET_BOOK_COUNT_STMT);
			pst.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return bookCount;
	}

	// public void buildQuery(String title, String author, String year, String
	// rating, String content) {
	// query = "SELECT * FROM BOOK WHERE Title LIKE '%" + title +
	// "%' AND Author LIKE '%" + author + "%' AND Year_Read LIKE '%" + year
	// + "%' AND Rating LIKE '%" + rating + "%' AND Content LIKE '%" + content +
	// "%';";
	// // System.out.println("made it to buildQuery.  query = " + query);
	// }

	public void setPrepareStatement(Book book) {
		// PreparedStatement pst = null;

		try {
			pst = getConnection().prepareStatement(SEARCH_STMT);
			pst.setString(1, "%" + book.getTitle() + "%");
			pst.setString(2, "%" + book.getAuthor() + "%");
			pst.setString(3, "%" + book.getYear() + "%");
			pst.setString(4, "%" + book.getRating() + "%");
			pst.setString(5, "%" + book.getContent() + "%");

			// pst.executeQuery();
			System.out.println(pst + " after search method");

		} catch (SQLException e) {
			e.printStackTrace();
		} // finally {
		// if (pst != null) {
		// try {
		// pst.close();
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
		// }
		// }
	}

	public PreparedStatement getPreparedStatement() {
		System.out.println("PST = " + pst.toString());
		return pst;

	}
	
	public Vector<String> getRow(){
		return row;
	}

	public void insertBook(Book book) {
		PreparedStatement pst = null;
		try {
			pst = getConnection().prepareStatement(INSERT_BOOK_STMT);
			pst.setInt(1, getMaxBookID() + 1);
			pst.setString(2, book.getTitle());
			pst.setString(3, book.getAuthor());
			pst.setString(4, book.getYear());
			pst.setString(5, book.getRating());
			pst.setString(6, book.getContent());
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void insertRow(String title, String author, String yearRead, String rating, String content) {
		// PreparedStatement pst = null;
		try {
			pst = getConnection().prepareStatement(INSERT_BOOK_STMT);
			pst.setInt(1, getMaxBookID() + 1);
			pst.setString(2, title);
			pst.setString(3, author);
			pst.setString(4, yearRead);
			pst.setString(5, rating);
			pst.setString(6, content);
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	// This will be called when I can find out how to get the book_ID from the
	// row
	public void deleteRow(int bookID) {
		PreparedStatement pst = null;
		String deleteStatement = "DELETE FROM BOOK WHERE BOOK_ID = " + bookID;
		try {
			pst = getConnection().prepareStatement(deleteStatement);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}
